package com.example.dailyrecord.base.entity;

import com.example.dailyrecord.util.FunctionUtils;

import java.util.List;
import java.util.stream.Collectors;

public class SQLBuilder {
    public static final String DESC = " DESC ";
    public static final String ASC = " ASC ";
    private static final String ORDER_BY = " order by ";
    private static final String LIMIT = " limit ";
    private StringBuilder cond;
    private String sql;

    private SQLBuilder() {
        this.cond = new StringBuilder();
    }

    public static SQLBuilder getInstance() {
        return new SQLBuilder();
    }

    public SQLBuilder clear() {
        this.cond = new StringBuilder();
        return this;
    }

    public SQLBuilder select(String... column) {
        this.cond.append("select ");
        return this.column(column);
    }

    public SQLBuilder update() {
        this.cond.append("update ");
        return this;
    }

    public SQLBuilder update(String table) {
        this.cond.append("update ");
        return this.table(table);
    }

    public SQLBuilder set() {
        this.cond.append(" set ");
        return this;
    }

    public SQLBuilder distinct() {
        this.cond.append(" distinct ");
        return this;
    }

    public SQLBuilder column(String... column) {
        this.cond.append(FunctionUtils.SPACE).append(String.join(",", column));
        return this;
    }

    public SQLBuilder alias(String aliasName) {
        this.cond.append(FunctionUtils.SPACE).append(aliasName);
        return this;
    }

    public SQLBuilder from(String table) {
        this.cond.append(" from ").append(table);
        return this;
    }

    public SQLBuilder table(String table) {
        this.cond.append(FunctionUtils.SPACE).append(table);
        return this;
    }

    public SQLBuilder appendTable(String table) {
        this.cond.append(FunctionUtils.SPACE).append(FunctionUtils.COMMA).append(table);
        return this;
    }

    public SQLBuilder leftJoin(String table) {
        this.cond.append(" left join ").append(table);
        return this;
    }

    public SQLBuilder rightJoin(String table) {
        this.cond.append(" right join ").append(table);
        return this;
    }

    public SQLBuilder innerJoin(String table) {
        this.cond.append(" inner join ").append(table);
        return this;
    }

    public SQLBuilder on(String cond) {
        this.cond.append(" on ").append(cond);
        return this;
    }

    public SQLBuilder where() {
        this.cond.append(" where ");
        return this;
    }

    public SQLBuilder eq(String field, Object value) {
        this.cond.append(field).append(Options.EQ.getOpt()).append(toSQLString(value));
        return this;
    }

    public SQLBuilder neq(String field, Object value) {
        this.cond.append(field).append(Options.NEQ.getOpt()).append(toSQLString(value));
        return this;
    }

    public SQLBuilder nl(String field) {
        this.cond.append(field).append(Options.NL.getOpt());
        return this;
    }

    public SQLBuilder nnl(String field) {
        this.cond.append(field).append(Options.NNL.getOpt());
        return this;
    }

    public SQLBuilder gt(String field, Object value) {
        this.cond.append(field).append(Options.GT.getOpt()).append(toSQLString(value));
        return this;
    }

    public SQLBuilder gte(String field, Object value) {
        this.cond.append(field).append(Options.GTE.getOpt()).append(toSQLString(value));
        return this;
    }

    public SQLBuilder lt(String field, Object value) {
        this.cond.append(field).append(Options.LT.getOpt()).append(toSQLString(value));
        return this;
    }

    public SQLBuilder lte(String field, Object value) {
        this.cond.append(field).append(Options.LTE.getOpt()).append(toSQLString(value));
        return this;
    }

    public SQLBuilder bt(String field, Object v1, Object v2) {
        this.cond.append(field).append(Options.BT.getOpt()).append(toSQLString(v1)).append(Options.AND.getOpt())
                .append(toSQLString(v2));
        return this;
    }

    public SQLBuilder nbt(String field, Object v1, Object v2) {
        this.cond.append(field).append(Options.NBT.getOpt()).append(toSQLString(v1)).append(Options.AND.getOpt())
                .append(toSQLString(v2));
        return this;
    }

    public SQLBuilder lk(String field, Object value) {
        this.cond.append(field).append(Options.LK.getOpt())
                .append(toSQLString(FunctionUtils.Percent + value + FunctionUtils.Percent));
        return this;
    }

    public SQLBuilder llk(String field, Object value) {
        this.cond.append(field).append(Options.LLK.getOpt()).append(toSQLString(FunctionUtils.Percent + value));
        return this;
    }

    public SQLBuilder rlk(String field, Object value) {
        this.cond.append(field).append(Options.RLK.getOpt()).append(toSQLString(value + FunctionUtils.Percent));
        return this;
    }

    public SQLBuilder nlk(String field, Object value) {
        this.cond.append(field).append(Options.NLK.getOpt())
                .append(toSQLString(FunctionUtils.Percent + value + FunctionUtils.Percent));
        return this;
    }

    public SQLBuilder nllk(String field, Object value) {
        this.cond.append(field).append(Options.NLLK.getOpt()).append(toSQLString(FunctionUtils.Percent + value));
        return this;
    }

    public SQLBuilder nrlk(String field, Object value) {
        this.cond.append(field).append(Options.NRLK.getOpt()).append(toSQLString(value + FunctionUtils.Percent));
        return this;
    }

    public SQLBuilder in(String field, String... ss) {
        this.cond.append(field).append(Options.IN.getOpt()).append(FunctionUtils.L_PARENTHESE)
                .append(String.join(FunctionUtils.COMMA, toSQLString(ss)))
                .append(FunctionUtils.R_PARENTHESE);
        return this;
    }

    public SQLBuilder nin(String field, String... ss) {
        this.cond.append(field).append(Options.NIN.getOpt()).append(FunctionUtils.L_PARENTHESE)
                .append(String.join(FunctionUtils.COMMA, toSQLString(ss)))
                .append(FunctionUtils.R_PARENTHESE);
        return this;
    }

    public SQLBuilder in(String field, List<String> list) {
        this.cond.append(field).append(Options.IN.getOpt()).append(FunctionUtils.L_PARENTHESE)
                .append(toSQLString(list))
                .append(FunctionUtils.R_PARENTHESE);
        return this;
    }

    public SQLBuilder nin(String field, List<String> list) {
        this.cond.append(field).append(Options.NIN.getOpt()).append(FunctionUtils.L_PARENTHESE)
                .append(String.join(FunctionUtils.COMMA, toSQLString(list)))
                .append(FunctionUtils.R_PARENTHESE);
        return this;
    }

    public SQLBuilder and() {
        this.cond.append(Options.AND.getOpt());
        return this;
    }

    public SQLBuilder or() {
        this.cond.append(Options.OR.getOpt());
        return this;
    }

    public SQLBuilder lClose() {
        this.cond.append(FunctionUtils.SPACE).append(FunctionUtils.L_PARENTHESE);
        return this;
    }

    public SQLBuilder rClose() {
        this.cond.append(FunctionUtils.SPACE).append(FunctionUtils.R_PARENTHESE);
        return this;
    }

    public SQLBuilder groupBy(String... column) {
        this.cond.append(" group by ").append(String.join(",", column));
        return this;
    }

    public SQLBuilder having(String cond) {
        this.cond.append(" having ").append(FunctionUtils.L_PARENTHESE)
                .append(cond).append(FunctionUtils.R_PARENTHESE);
        return this;
    }

    public SQLBuilder cond(String opt, Object value) {
        this.cond.append(opt).append(FunctionUtils.SPACE).append(toSQLString(value));
        return this;
    }

    public SQLBuilder sort(String field, String order) {
        this.cond.append(ORDER_BY).append(field).append(FunctionUtils.SPACE).append(order);
        return this;
    }

    public SQLBuilder appendSort(String field, String order) {
        this.cond.append(FunctionUtils.COMMA).append(field).append(FunctionUtils.SPACE).append(order);
        return this;
    }

    public SQLBuilder limit(int offset, int limit) {
        this.cond.append(LIMIT).append(offset).append(FunctionUtils.COMMA).append(limit);
        return this;
    }

    public SQLBuilder limit(int limit) {
        this.cond.append(LIMIT).append(limit);
        return this;
    }

    private static String toSQLString(String value) {
        return FunctionUtils.SINGLE_QUOTATION_MARK + value.replace("'", "\\'") + FunctionUtils.SINGLE_QUOTATION_MARK;
    }

    private static String toSQLString(Object value) {
        if (value instanceof String) {
            return toSQLString((String) value);
        } else {
            return String.valueOf(value);
        }
    }

    private static String[] toSQLString(String[] ss) {
        for (int i = 0; i < ss.length; i++) {
            ss[i] = toSQLString(ss[i]);
        }
        return ss;
    }

    private static String toSQLString(List<String> list) {
        return list.stream().map(SQLBuilder::toSQLString).collect(Collectors.joining(","));
    }

    public String getSql() {
        return this.sql = this.cond.toString();
    }
}
